﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Chaotic.DataBase.DataAccess;
using Chaotic.DataBase.DBAccessLibrary.DBAccessLibrary;
using Chaotic.DataBase.DBAccessLibrary.Interface;
using Chaotic.DataBase.DBAccessLibrary.Model;

namespace Chaotic.DataBase.DBAccessLibrary.Grammar
{
    internal class MysqlGrammar : DBGrammar, IGrammar, ILocalGrammar
    {
        public string GetChTableSql
        {
            get
            {
                if (string.IsNullOrEmpty(_xml.ChDescription))
                {
                    return "SELECT TABLENAME,CHINESETABLENAME as CHINATABLENAME,FIELDNAME as COLUMNNAME,CHINESENAME as CHINACOLUMNNAME FROM T_FIELD_DICTIONARY order by TABLENAME";
                }
                return _xml.ChDescription;
            }
        }

        public string? IsExistChTableSql
        {
            get
            {
                string? result = string.Empty;
                if (string.IsNullOrEmpty(_xml.ChDescription))
                {
                    result = "select name from sysobjects where name='T_FIELD_DICTIONARY'";
                }
                return result;
            }
        }

        public MysqlGrammar(ConnectionModel? connect)
            : base(connect)
        {
        }

        private string? GetTypeFilter(FieldEntity? field, string sql)
        {
            string? text = field.TypeName;
            int.TryParse(field.Length, out var result);
            string? text2 = field.Length;
            string text3 = field.TypeName.ToUpper();
            if (text3 == "VARCHAR2" || text3 == "VARCHAR")
            {
                text = "VARCHAR";
                text2 = "(" + field.Length + ")";
            }
            if ((text3 == "NUMBER" && result < 9) || text3 == "INT")
            {
                text = "INT";
                text2 = "";
            }
            else if (text3 == "NUMBER" && result >= 9)
            {
                text = "DECIMAL";
                text2 = "(18,2)";
            }
            if (text3 == "TIMESTAMP" || text3 == "DATE")
            {
                text2 = "";
            }
            if (!field.IsNull)
            {
                sql += " Not Null";
            }
            return string.Format(sql, field.TableName, field.Name, text, text2);
        }

        public string? AddColumn(FieldEntity? field)
        {
            string text = "ALTER TABLE [{0}] ADD {1} {2}{3}";
            if (!string.IsNullOrEmpty(field.DefaultValue))
            {
                text = text + " DEFAULT " + field.DefaultValue;
            }
            return GetTypeFilter(field, text);
        }

        public IEnumerable<string?> EditColumn(FieldEntity? field)
        {
            List<string?> list = new List<string?>();
            if (field.IsChange((FieldEntity o) => o.ColumnName))
            {
                list.Add($"EXEC SP_RENAME '{field.TableName}.{field.GetNoChangeValue((FieldEntity o) => o.ColumnName)}','{field.ColumnName}','COLUMN'");
            }
            if (field.IsChange((FieldEntity o) => o.DefaultValue))
            {
                string? sql = $"select b.name from syscolumns a,sysobjects b where a.id=object_id('{field.TableName}') and b.id=a.cdefault and a.name ='{field.Name}' and b.name like 'DF%' ";
                DataSet? dataSet = DBOperatorFactory.GetOperator.ExecSqlToDS(sql, _connect);
                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    string? item = $"alter table {field.TableName} drop constraint {row[0]} ";
                    list.Add(item);
                }
                list.Add(GetTypeFilter(field, "ALTER TABLE [{0}] ALTER COLUMN {1} {2}{3}"));
                if (!string.IsNullOrEmpty(field.DefaultValue))
                {
                    sql = string.Format("ALTER TABLE [{0}] ADD CONSTRAINT DF_{1}_{0} DEFAULT({2}) FOR {1}", field.TableName, field.Name, field.DefaultValue);
                    list.Add(sql);
                }
            }
            else
            {
                list.Add(GetTypeFilter(field, "ALTER TABLE [{0}] ALTER COLUMN {1} {2}{3}"));
            }
            return list;
        }

        public IEnumerable<string?> RemoveColumn(FieldEntity field)
        {
            List<string?> list = new List<string?>();
            if (field.IsForeignKey || field.IsPrimaryKey || !string.IsNullOrEmpty(field.DefaultValue))
            {
                string? sql = $"select b.name from syscolumns a,sysobjects b where a.id=object_id('{field.TableName}') and b.id=a.cdefault and a.name ='{field.Name}' and b.name like 'DF%' ";
                DataSet? dataSet = DBOperatorFactory.GetOperator.ExecSqlToDS(sql, _connect);
                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    string? item = $"alter table {field.TableName} drop constraint {row[0]} ";
                    list.Add(item);
                }
            }
            list.Add($"ALTER TABLE [{field.TableName}] DROP COLUMN {field.TransName}");
            return list;
        }

        public IEnumerable<string?> EditPrimary(TableEntity table)
        {
            List<string?> list = new List<string?>();
            string text = table.PrimaryKeyName;
            string[] array = (from o in table.FileNodes
                              where o.IsPrimaryKey
                              select o.Name).ToArray();
            if (array.Length > 0)
            {
                if (!string.IsNullOrEmpty(text))
                {
                    string? item = $"alter table [{table.Name}] drop constraint {text} ";
                    list.Add(item);
                }
                else
                {
                    text = "PK" + array[0];
                }
                list.Add($"ALTER TABLE [{table.Name}] ADD CONSTRAINT {text} PRIMARY KEY({string.Join(",", array)})");
            }
            return list;
        }

        public string? SelectTop(TableEntity table, int count)
        {
            string format = "SELECT TOP {1} * FROM {0}";
            return string.Format(format, table.TransName, count);
        }

        public string AppendTabText(string text)
        {
            return TabText(text);
        }

        public string GetErrorInfo(string errorMsg)
        {
            return string.Empty;
        }

        public string? GetProcParamInfo(ProcEntity entity)
        {
            if (entity == null)
            {
                return string.Empty;
            }
            string arg;
            if (entity.ChildNodes.Count > 0)
            {
                List<string> list = new List<string>();
                foreach (ProcParamEntity? childNode in entity.ChildNodes)
                {
                    if (childNode.Status == "IN")
                    {
                        list.Add($"{Environment.NewLine}[{childNode.Name}({childNode.TypeName})]");
                    }
                    else
                    {
                        list.Add($"{Environment.NewLine}[{childNode.Name}({childNode.TypeName})-OUT]");
                    }
                }
                arg = "(" + string.Join(",", list.ToArray()) + ")";
            }
            else
            {
                arg = "()";
            }
            return $"{"EXEC"} {entity.Name}{arg}";
        }

        public string GetPageingSql(string sql, decimal index, decimal count)
        {
            string format = "SELECT tab.* FROM (SELECT {0} ROWNUMBER,t.* FROM (" + Environment.NewLine + "{1}" + Environment.NewLine + ") t) tab  WHERE tab.ROWNUMBER >= " + index + " and tab.ROWNUMBER < " + count;
            int length = sql.IndexOf("FROM", StringComparison.CurrentCultureIgnoreCase);
            string text = sql.Substring(0, length);
            if (text.IndexOf("count(") > 0 || text.IndexOf("sum(") > 0)
            {
                return sql;
            }
            if (sql.IndexOf("ORDER BY", StringComparison.CurrentCultureIgnoreCase) > 0)
            {
                sql = sql.Trim();
                if (sql.Substring(0, 6).ToLower() == "select" && !sql.Substring(6).Trim().StartsWith("TOP "))
                {
                    sql = sql.Insert(6, " TOP " + count + " ");
                }
            }
            return string.Format(format, "ROW_NUMBER() OVER(ORDER BY GETDATE())", sql);
        }

        protected override string GetRowNumUpdate(string tableName, string where, string column, string? rownumber)
        {
            return string.Format("UPDATE T1 SET {1} FROM ({0}) T1 WHERE T1.ROWNUMBER={2}", string.Format("SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) ROWNUMBER,{0}.* FROM {0}", tableName) + " " + where, column, rownumber);
        }

        protected override string GetRowNumDelete(string tableName, string where, string? rownumber)
        {
            return
                $"WITH [T1] AS ({string.Format("SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) ROWNUMBER,{0}.* FROM {0}", tableName) + " " + where}) DELETE FROM [T1] WHERE T1.ROWNUMBER={rownumber}";
        }

        public DatabaseType GetConnectionStr(out string connectStr)
        { 
            // 允许用户变量  命令超时
            connectStr = "Server={3};Database={0};Uid={1};Pwd={2};Allow User Variables=True;"; 
            connectStr = string.Format(connectStr, _connect.DataBase, _connect.UserID, _connect.PassWord, _connect.Server);
            return DatabaseType.Mysql;
        }
    }
}